# Copyright (C) 2026. Logiciel élaboré par l'État, via le DEPS.
# Nom des auteures : Flore Cornuet et Camille Parent, DEPS.
# Ce programme informatique a été développé par le DEPS.
# Il permet de produire les résultats du Culture Études 1-2026.

# Ce programme a été exécuté en septembre 2026 avec la version 4.5.3 de R.
# La publication peut être consultée sur le site du Deps. 
# Ce programme utilise les données du pass Culture et du recensement de la population 2022.

# Bien qu'il n'existe aucune obligation légale à ce sujet, les utilisateurs de ce programme sont
# invités à signaler au DEPS leurs travaux issus de la réutilisation de ce code, ainsi que les
# éventuels problèmes ou anomalies qu'ils y rencontreraient, en écrivant à contact.deps@culture.gouv.fr .
# Ce logiciel est régi par la licence EUPL V1.2 soumise au droit français et européen, respectant
# les principes de diffusion des logiciels libres. Vous pouvez utiliser, modifier et/ou redistribuer
# ce programme sous les conditions de la licence EUPL V1.2, telle que diffusée sur
# https://eupl.eu/1.2/fr/ .
# En contrepartie de l'accessibilité au code source et des droits de copie, de modification et de
# redistribution accordés par cette licence, il n'est offert aux utilisateurs qu'une garantie
# limitée. Pour les mêmes raisons, seule une responsabilité restreinte pèse sur les auteures du
# programme, le titulaire des droits patrimoniaux et les concédants successifs.
# À cet égard l'attention de l'utilisateur est attirée sur les risques associés au chargement, à
# l'utilisation, à la modification et/ou au développement et à la reproduction du logiciel par
# l'utilisateur étant donné sa spécificité de logiciel libre, qui peut le rendre complexe à
# manipuler et qui le réserve donc à des développeurs et des professionnels avertis possédant des
# connaissances informatiques approfondies. Les utilisateurs sont donc invités à charger et tester
# l'adéquation du logiciel à leurs besoins dans des conditions permettant d'assurer la sécurité de
# leurs systèmes et ou de leurs données et, plus généralement, à l'utiliser et l'exploiter dans les
# mêmes conditions de sécurité.



# _____________________________________________________________
# Répartition des livres achetés (used/reimbursed) par genre
# littéraire (GTL niveau 1 et niveau 2) et civilité
# Champ : chèques de 300 € (deposit_amount == 300), créés à partir
# du 21/05/2021, expirés avant le 28/02/2025
# _____________________________________________________________

# Packages ####
library(arrow)
library(duckdb)
library(DBI)
library(glue)
library(dplyr)
library(tidyr)
library(kableExtra)



# Paramètre : montant du chèque ####
montant_chq <- 300

# Connexion DuckDB ####
con <- dbConnect(duckdb())
dbExecute(con, "SET threads = 4")
dbExecute(con, "SET preserve_insertion_order = false")
dbExecute(con, "SET memory_limit = '16GB'")

# Charger la table livres (partitionnée par user_civility) ####
dbExecute(con, sprintf(
  "CREATE OR REPLACE TABLE livres AS SELECT * FROM read_parquet('%s', hive_partitioning=true)",
  paste0(chemin_intermediaires, "livres/**/*.parquet")
))

# Vérification rapide des colonnes disponibles
colnames(dbGetQuery(con, "SELECT * FROM livres LIMIT 0"))

# Vérification : distribution des montants de chèques sur le champ
dbGetQuery(con, sprintf("
  SELECT deposit_amount, COUNT(*) AS nb_deposits
  FROM read_parquet('%sexp_ministry__deposit/*.parquet')
  WHERE deposit_creation_date >= '2021-05-21'
    AND deposit_creation_date <  '2025-02-28'
    AND deposit_expiration_date < '2025-02-28'
  GROUP BY deposit_amount
  ORDER BY nb_deposits DESC
", chemin_donnees))

# _____________________________________________________________
# GTL niveau 2 x civilité ####
# _____________________________________________________________

df_gtl2_civ_chq <- dbGetQuery(con, sprintf("
  WITH deposit_flag AS (
    SELECT
      deposit_id,
      deposit_amount,
      CASE WHEN deposit_creation_date >= '2021-05-21'
            AND deposit_creation_date <  '2025-02-28' THEN 1 ELSE 0 END AS champ_creation,
      CASE WHEN deposit_expiration_date < '2025-02-28' THEN 1 ELSE 0 END AS champ_expiration
    FROM read_parquet('%sexp_ministry__deposit/*.parquet')
  ),
  base AS (
    SELECT
      l.gtl_label_level_2,
      l.user_civility,
      COUNT(l.booking_id) AS nb_livres,
      COUNT(l.booking_id) * 100.0 / SUM(COUNT(l.booking_id)) OVER (PARTITION BY l.user_civility) AS pct
    FROM livres l
    INNER JOIN deposit_flag d ON l.deposit_id = d.deposit_id
    WHERE d.champ_creation = 1 AND d.champ_expiration = 1
      AND d.deposit_amount = %s
    GROUP BY l.gtl_label_level_2, l.user_civility
  )
  SELECT *,
    SUM(pct) OVER (PARTITION BY user_civility ORDER BY nb_livres DESC) AS pct_cumul
  FROM base
  ORDER BY user_civility, nb_livres DESC
", chemin_donnees, montant_chq))

tableau_gtl2_civ <- df_gtl2_civ_chq |>
  mutate(pct = round(pct, 1)) |>
  select(gtl_label_level_2, user_civility, pct) |>
  pivot_wider(names_from = user_civility, values_from = pct) |>
  arrange(desc(across(where(is.numeric), sum)))

tableau_gtl2_civ

tableau_gtl2_civ |>
  kbl(caption = glue("Répartition des livres achetés par genre littéraire (niveau 2) et civilité (%) — chèques de {montant_chq} €"),
      col.names = c("Genre littéraire", names(tableau_gtl2_civ)[-1])) |>
  kable_styling(full_width = FALSE, bootstrap_options = c("striped", "hover"))

# _____________________________________________________________
# GTL niveau 1 x civilité ####
# _____________________________________________________________

df_gtl1_civ_chq <- dbGetQuery(con, sprintf("
  WITH deposit_flag AS (
    SELECT
      deposit_id,
      deposit_amount,
      CASE WHEN deposit_creation_date >= '2021-05-21'
            AND deposit_creation_date <  '2025-02-28' THEN 1 ELSE 0 END AS champ_creation,
      CASE WHEN deposit_expiration_date < '2025-02-28' THEN 1 ELSE 0 END AS champ_expiration
    FROM read_parquet('%sexp_ministry__deposit/*.parquet')
  ),
  base AS (
    SELECT
      l.gtl_label_level_1,
      l.user_civility,
      COUNT(l.booking_id) AS nb_livres,
      COUNT(l.booking_id) * 100.0 / SUM(COUNT(l.booking_id)) OVER (PARTITION BY l.user_civility) AS pct
    FROM livres l
    INNER JOIN deposit_flag d ON l.deposit_id = d.deposit_id
    WHERE d.champ_creation = 1 AND d.champ_expiration = 1
      AND d.deposit_amount = %s
    GROUP BY l.gtl_label_level_1, l.user_civility
  )
  SELECT *,
    SUM(pct) OVER (PARTITION BY user_civility ORDER BY nb_livres DESC) AS pct_cumul
  FROM base
  ORDER BY user_civility, nb_livres DESC
", chemin_donnees, montant_chq))

tableau_gtl1_civ <- df_gtl1_civ_chq |>
  mutate(pct = round(pct, 1)) |>
  select(gtl_label_level_1, user_civility, pct) |>
  pivot_wider(names_from = user_civility, values_from = pct) |>
  arrange(desc(across(where(is.numeric), sum)))

tableau_gtl1_civ

tableau_gtl1_civ |>
  kbl(caption = glue("Répartition des livres achetés par genre littéraire (niveau 1) et civilité (%) — chèques de {montant_chq} €"),
      col.names = c("Genre littéraire", names(tableau_gtl1_civ)[-1])) |>
  kable_styling(full_width = FALSE, bootstrap_options = c("striped", "hover"))

# Déconnexion ####
# dbDisconnect(con, shutdown = TRUE)


# Fonction générique pour copier un data.frame/tibble dans le presse-papier,
# prêt à coller dans Excel (colonnes tabulées, décimales avec virgule si besoin)

write_clipboard <- function(df, dec = ",") {
  df |>
    write.table(
      "clipboard",
      sep = "\t",
      row.names = FALSE,
      dec = dec,          # "," pour Excel FR, "." si Excel en config EN
      na = "",
      fileEncoding = "UTF-8"
    )
}

# Utilisation :
write_clipboard(tableau_gtl2_civ)
write_clipboard(tableau_gtl1_civ)
